//
//  DBTool.swift
//  Ledger
//
//  Created by Sky on 2019/12/27.
//  Copyright © 2019 Sky. All rights reserved.
//

import Foundation
import FMDB


public class DBTool {
    public static let shared = DBTool()
    public private(set) var dbQueue: FMDatabaseQueue!
    
    /// 数据库路径 - readOnly 只读
    public private(set) var dbPath: String = ""
    private var dbName = "" // = "ledgerDB.db"
    
    
    /// 初始化数据库
    public func initDataBase(dbName db_name: String) {
        dbName = db_name == "" ? "dbName.db" : db_name
        let path = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!.appending("/" + (Bundle.main.infoDictionary?["CFBundleName"] as? String ?? "DataBase") + "/")
        if !FileManager.default.fileExists(atPath: path) {
            try! FileManager.default.createDirectory(atPath: path, withIntermediateDirectories: true, attributes: nil)
        }
        dbPath = path + dbName
        
        dbQueue = FMDatabaseQueue(path: dbPath)
        if dbQueue != nil {
            print("创建数据库成功！ dbPath =", dbPath)
        } else {
            print("创建数据库失败！")
        }
    }
    
    
    
    

}

// MARK: -  insert
extension DBTool {
    
    public func insert(_ sql: String) -> Int {
        var lastID = 0, nonQuery = 0
        update(sql: sql, argsIn: nil, lastID: &lastID, nonQuery: &nonQuery)
        return lastID
    }
    
    
    
    @discardableResult
    public func update(sql: String, argsIn arr: [Any]? = nil, lastID: inout Int, nonQuery: inout Int) -> Bool {
        print("sql = \(sql), arr = \(arr ?? [])")
        var isSucc = false, lastID_In = -1, nonQuery_In = 0
        dbQueue.inTransaction { (db, rollback) in
            if (!db.executeUpdate(sql, withArgumentsIn: arr ?? [])) {
                rollback.pointee = true
                print("rollback!!!")
                return
            }
            lastID_In = Int(db.lastInsertRowId)
            nonQuery_In = Int(db.changes)
            isSucc = true
            print("success")
        }
        lastID = lastID_In
        nonQuery = nonQuery_In
        
        return isSucc
    }
    
    @discardableResult
    public func update(sql: String) -> Bool {
//        dbQueue.inTransaction({ (db, stop) in
//            if db.executeUpdate(sql, withArgumentsIn: []) {
//                print("success")
//            } else {
//                print("failure")
//            }
//        })
        
        dbQueue.inTransaction { (db, stop) in
            if db.executeStatements(sql) {
                print("success")
            } else {
                print("failure")
            }
        }
        
        return false
    }
    
    
    @discardableResult
    public func executeStatements(sql: String) -> Bool {
        dbQueue.inTransaction { (db, stop) in
            let isSucc = db.executeStatements(sql) { (dict) -> Int32 in
                print(dict)
                
                
                return 0
            }
            isSucc ? skprint("success") : skprint("failure")
        }
        
        
        
        return true
    }
}


// MARK: -  query / select
extension DBTool {
    
    public func query(sql: String, argsIn params: [Any]? = nil) -> [[String: Any]] {
        var arr = [[String: Any]]()
        dbQueue.inDatabase { (db) in
            guard let rs = db.executeQuery(sql, withArgumentsIn: params ?? []) else { return }
            
            while rs.next() {
                var dict = [String: Any]()
                for i in 0..<rs.columnCount {
                    dict[rs.columnName(for: i)!] = rs.object(forColumnIndex: i)
                }
                arr.append(dict)
            }
            rs.close()
        }
        
        return arr
    }
}


// MARK: -  独立功能
public extension DBTool {
    
    func inTranaction(block: (FMDatabase, UnsafeMutablePointer<ObjCBool>) -> ()) {
        dbQueue.inTransaction(block)
    }
    
}




// MARK: -  test
extension DBTool {
    
    /// 给表添加字段
    func addColumn(tableName: String, columns: [String], columnTypes: [String]) {
        var sql = ""
        for i in 0 ..< columns.count {
            sql.append(" alter table \(tableName) add \(columns[i]) \(columnTypes[i]); \n")
        }
        update(sql: sql)
    }
}






